Summary

The Livelihoods sub-goal is calculated using coastal employment data from the National Ocean Economics Program, and State-wide employment from the Bureau of Labor Statistics.

Data Sources

National ocean Economics Program (NOEP)

Downloaded: Received from Pat Johston on October 3, 2017.
Description: Total number of jobs and wages per sector for RI, ME, MA, CT, NY and NH counties from 2005 to 2013. The data also include number of establishments and GDP for each sector - state - year.
Native data resolution: County level
Time range: 2005 - 2014
Format: Tabular

US Bureau of Labor Statistics

Downloaded: May 17, 2016
Description: Total Annual Employment (in thousands) per State from 1990 to 2015
Native data resolution: State level
Time Range: 2005 to 2015
Format: Tabular

Load data

NOEP data

Two sets of data are provided, one for the timeframe 1990 - 2004 and the other from 2005 - 2014. NOEP recommends not using these two together due to inconsistincies across the two, especially in regards to GDP. While both are read in here, we only use the 2005 - 2014 data to calculate the LIV goal for now.

## Read in raw data which came in .xlsx format with 2 sheets. 

## read in NOEP sheet for all data 1990 - 2004
noep_data_1 <- read_excel(file.path(dir_anx, '_raw_data/NOEP/New_England_ocean_series.xlsx'), sheet = "NOEP")
  
## read in ENOW sheet which contains data for 2005 - 2014 
noep_data_2 <- read_excel(file.path(dir_anx, '_raw_data/NOEP/New_England_ocean_series.xlsx'), sheet = "ENOW")

## moving forward just with the more recent data series

data <- noep_data_2 %>%
       filter(Sector=='All Ocean Sectors') %>%
        mutate(Employment     = as.numeric(gsub(",","",.$Employment)),
               Establishments = as.numeric(gsub(",","",.$Establishments)),
               Wages          = as.numeric(gsub(",","",.$Wages)),
               Wages_2012     = as.numeric(gsub(",","",.$Wages_2012)),
               GDP            = as.numeric(gsub(",","",.$GDP)),
               GDP_2012       = as.numeric(gsub(",","",.$GDP_2012)))


DT::datatable(data, rownames=F, caption = 'Employment in the Tourism & Recreation sector of Coastal Jobs in New England provided by NOEP')

BLS data

The Bureau of Labor Statistics has a clunky online data query that is less than ideal for downloading data. They do have an API and I found a couple different R packages for accessing it. For now, I’m using blscrapeR which fortunately has a function to access state data by month. Since we are interested in annual averages, it requires some additional aggregating but seems to work quite well and is able to access data from 1976 - current, although we just need 2005 - 2014 to match the NOEP data.

# the R package is a little clunky in that it requires a query using months + years.
out <- c()

for(i in 1990:2016){
mths <- c(paste0("January ", i), paste0("February ", i), paste0("March ", i), paste0("April ", i), paste0("May ", i), 
                 paste0("June ", i), paste0("July ", i), paste0("August ", i), paste0("September ", i), paste0("October ", i), 
          paste0("November ", i), paste0("December ", i))

out <- c(mths, out)
}

## use get_bls_state to query a text file of state data
st_emp <- get_bls_state(date_mth = out, seasonality = FALSE) %>%
  separate(month, into = c("Year", "month", "day"), sep = "-") %>%
  filter(state %in% c("New York", "Massachusetts", "Connecticut", "Rhode Island", "Maine", "New Hampshire")) %>%
  group_by(state, Year, state_abb) %>%
  mutate(yr_avg_employed        = mean(employed),
         yr_avg_employed_rate   = mean(employed_rate),
         yr_avg_unemployed_rate = mean(unemployed_rate)) %>%
  ungroup() %>%
  select(state, Year, yr_avg_employed, yr_avg_employed_rate, yr_avg_unemployed_rate) %>%
  rename(State = state) %>%
  mutate(Year = as.numeric(Year)) %>%
  distinct()

Methods

Jobs

The livelihoods jobs score, \(j '\), is calculated using the following equation;

\[j ' = \frac{\frac{C_{i}}{C_{r}}}{\frac{T_{i}}{T_{r}}}\]

where \(C_{i}\) is the number of coastal jobs in year i, \(C_{r}\) is the number of coastal jobs in 2005, \({T_{i}}\) is the total number of jobs statewide for year i and \({T_{r}}\) is the number of jobs statewide in 2005.

jobs <- data %>%
      rename(coast_jobs = Employment) %>%
      mutate(County     = ifelse(grepl("All", County), "Statewide", County),
             hist_jobs  = coast_jobs[which.min(Year)]) %>%
      filter(Sector == "All Ocean Sectors",
             County == "Statewide") %>%
      left_join(st_emp, by = c('State', 'Year')) %>%
      mutate(cst_chg = coast_jobs/hist_jobs, #use historical reference point from 2005
                 hist_st_jobs = yr_avg_employed[which.min(Year)],
                 st_chg = yr_avg_employed/hist_st_jobs,
                 score = ifelse((cst_chg/st_chg) > 1, 1, cst_chg/st_chg)) %>%
      as.data.frame() %>%
      select(State, Year, hist_jobs, cst_chg, hist_st_jobs, st_chg, score) %>%
      unique()

jobs_plot <- ggplot(jobs, aes(x = Year, y = score, group=State)) +
  ggtheme_plot +
  geom_point(aes(color = State)) +
            geom_line(aes(color = State)) +
            scale_y_continuous(limits = c(0, 1)) +
            ggtitle("Jobs Scores for the Northeast") +
  labs(y = 'Score')

ggplotly(jobs_plot)

ggsave('figs/jobs_scores.png')

Wages

The livelihoods wages score, \(g'\) is calculated using the following equation;

\[g ' = \frac{\frac{C_{i}}{C_{r}}}{\frac{T_{i}}{T_{r}}}\]

These wage statistics came from BLS.gov. I had to manually query their online database and copy and paste the data for each county into an excel sheet (bls_wages.csv). I have not

#now that we can get wages using the blscrapeR package, we don't need to use this bls_wages.csv that I had previously downloaded. I did compare this dataset to the one retrieved from the API and they are the same. In the future we can likely delete the bls_wages.csv data but I'm holding onto it for now.
#old_bls_wages <- read.csv('bls_wages.csv', stringsAsFactors=FALSE)

wage_codes <- read_rtf('ne_wages_codes') %>%
  gsub(pattern = "E", replacement = " E") %>%
  strsplit(split = " ") %>%
  unlist()

state_lookup <- read_csv("bls_code_lookup.csv")

#only 50 seriesIDs can be submitted to the API at once. Since there are more than 50 seriesIDs we need to do it in batches
bls_data_1 <- bls_api(wage_codes[2:51],
              startyear = 2005, endyear = 2014, registrationKey = "BLS_KEY") %>%
  as.data.frame()
bls_data_2 <- bls_api(wage_codes[52:102],
              startyear = 2005, endyear = 2014, registrationKey = "BLS_KEY") %>%
  as.data.frame()
bls_data_3 <- bls_api(wage_codes[103:length(wage_codes)],
              startyear = 2005, endyear = 2014, registrationKey = "BLS_KEY") %>%
  as.data.frame()

#combine them all
bls_wages <- bls_data_1 %>%
  rbind(bls_data_2) %>%
  rbind(bls_data_3) %>%
  mutate(seriesID = as.character(.$seriesID)) %>%
  left_join(state_lookup, by = c("seriesID" = "code")) %>%
  filter(county == "Statewide") %>%
  group_by(state, year) %>%
  mutate(annual_wage = sum(value)) %>%
  select(year, state, annual_wage) %>%
  distinct()
  

wages <- data%>%
          mutate(County      = ifelse(grepl("All", County), "Statewide", County),
                 coast_wages = as.numeric(gsub(",", "", Wages_2012)),
                 hist_wages  = coast_wages[which.min(Year)]) %>%
          dplyr::select(-Employment, -Wages, -Wages_2012, -GDP, -GDP_2012) %>%
          filter(County == "Statewide") %>%
          left_join(bls_wages, by = c("State" = "state", "Year" = "year")) %>%
          mutate(w_cst_chg     = coast_wages/hist_wages, #use historical reference point from 2005
                 hist_st_wages = annual_wage[which.min(Year)],
                 w_st_chg      = annual_wage/hist_st_wages,
                 wages_score   = ifelse((w_cst_chg/w_st_chg) > 1, 1, w_cst_chg/w_st_chg))

#by state

s_plot <- ggplot(wages,aes(x = Year, y = wages_score, group=State)) +
  ggtheme_plot +
  geom_point(aes(color = State)) +
            geom_line(aes(color = State)) +
            scale_y_continuous(limits = c(0, 1)) + 
            ggtitle("Wages Scores for the Northeast Regions") +
  labs(y = 'Score')


ggplotly(s_plot)

ggsave('figs/state_wages_scores.png')
            
#by county
#             
# c    <- wages%>%
#       filter(County != 'Statewide') 
# 
# c_plot <- ggplot(c,aes(x = Year,y = wages_score, group=County))+
#   ggtheme_plot+
#   geom_point(aes(color = County)) +
#             geom_line(aes(color = County))+
#             scale_y_continuous(limits = c(0,1))+
#             ggtitle("Wages Scores for the Northeast Regions")+
#   labs(y = 'Score')
#             
#            
# ggplotly(c_plot)
# 
# ggsave('figs/wages_county_scores.png')

Results

Now to calculate the livelihoods sub goal we need to bring jobs and wages together

# Combine wages and jobs data

liv <- jobs%>%
        left_join(wages)%>%
       mutate(liv_score = (score + wages_score)/2)

liv_plot <- ggplot(liv, aes(x = Year, y = liv_score, group = County))+
  ggtheme_plot+
  geom_point(aes(color = County)) +
  geom_line(aes(color = County))+
  scale_y_continuous(limits = c(0, 1))+
  ggtitle("Livelihood Scores for the Northeast Regions")+
  labs(y = 'Score')
            
            
#aggregate by state (take the mean)
            
liv_st <- liv%>%
            group_by(State, Year)%>%
            summarise(st_score = mean(liv_score, na.rm=T))

liv_st_plot <- ggplot(liv_st, aes(x = Year, y = st_score, group = State)) +
  ggtheme_plot +
  geom_point(aes(color = State)) +
            geom_line(aes(color = State)) +
            scale_y_continuous(limits = c(0,1)) +
            ggtitle("Livelihood Scores for the Northeast Regions")+ 
  labs(y = 'Score')

ggplotly(liv_st_plot)

ggsave('figs/liv_scores.png')

Citation information

National Ocean Economics Program. Ocean Economic Data by Sector & Industry., ONLINE. 2012. Available: http://www.OceanEconomics.org/Market/oceanEcon.asp [17 May 2016]

Bureau of Labor Statistics, U.S. Department of Labor, Quarterly Census of Employment and Wages. 7/24/2016. http://www.bls.gov/cew/](http://www.bls.gov/cew/).

OHI-Northeast: Livelihoods

Compiled on Tue Oct 10 12:00:23 2017 by afflerbach